use master 
go

create database InfoPackage
on
(
	Name = 'InfoPackage',
	FileName = 'E:\InfoPackage.mdf',
	Size = 10MB,
	MaxSize = 80Mb,
	FileGrowth = 10%
)

log on
(
	Name = 'InfoPackage_log',
	FileName = 'E:\InfoPackage_log.ldf',
	Size = 5Mb,
	MaxSize = 20Mb,
	FileGrowth = 5%
)
go

use InfoPackage
go

Create table Class
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(10) unique not null
)

go

Create table Student
(
	StuID int primary key identity(1,1),
	ClassID int foreign key references CLass(ClassID),
	StuName nvarchar(5) not null,
	StuSex char(2) check(StuSex in('男', '女')),
	StuBirth date,
	StuPhone varchar(11) unique not null
	/* StuAddress nvarchar(120) */
)

go

Create table Course
(
	CourseID int primary key identity(1, 1),
	CourseName nvarchar(15) unique not null,
	CourseCredit int not null default ('1') check(CourseCredit >= 1 and CourseCredit <= 5)
)

go

Create table Score
(
	ScoreID int identity(1, 1), -- primary key
	StuID int, -- foreign key... references Student(StuID)
	Course int, -- foreign key... references Course(CourseID)
	Score decimal(5 ,2) unique not null 
)

go

/* last step */

Alter table Student
Add StudentAddress nvarchar(120)

go

Alter table Score
Add constraint FK_StuID foreign key(StuID) references Student(StuID)

go

Alter table Score
Add constraint FK_Course foreign key(Course) references Course(CourseID)



